﻿using System.Collections.Generic;
using GCTrackableRace.Interfaces;
using System.IO;
using System.Data.SQLite;
using System.Data;
using GcTrackableRace.Entities;

namespace GcTrackableRace.Persistence
{
	public class GcTrackableRacePersistence: BaseSqlitePersistence ,IGcTrackableRacePersistence
    {
		private readonly string _GcTrackableRaceDbFileNameAndLocation;

		public GcTrackableRacePersistence(string gcTrackableRaceDbFileNameAndLocation)
        {
			_GcTrackableRaceDbFileNameAndLocation = gcTrackableRaceDbFileNameAndLocation;
        }

		public bool DoesPersistenceExist()
		{
			return File.Exists(_GcTrackableRaceDbFileNameAndLocation);
		}

		public void CreatePersistence()
		{
			CreateDbFile();
			CreateTables();
		}

		public IEnumerable<ILocationInfo> GetAllLocationInfos()
        {
			const string sqlStatement =
				"Select Latitude, Longitude, CountryCode, CountryName, StateName, StateCode, Area, PostalCode from LocationInfo";

			var locationInfos = new List<ILocationInfo>();

			using (var conn = new SQLiteConnection(GetConnectionString(_GcTrackableRaceDbFileNameAndLocation)))
			{
				var cmd = new SQLiteCommand(sqlStatement, conn);

				SQLiteDataReader reader = null;

				conn.Open();

				try
				{
					reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

					while (reader.Read())
					{
						locationInfos.Add(
							new LocationInfo
							{
								Latitude = double.Parse(reader["Latitude"].ToString()),
								Longitude = double.Parse(reader["Longitude"].ToString()),
								CountryCode = reader["CountryCode"].ToString(),
								CountryName = reader["CountryName"].ToString(),
								StateCode = reader["StateCode"].ToString(),
								StateName = reader["StateName"].ToString(),
								Area = reader["Area"].ToString(),
								PostalCode = reader["PostalCode"].ToString()
							}
						);
					}
				}
				finally
				{
					if (reader != null)
						reader.Close();
				}
			}

			return locationInfos;
		}



		public void PersistLocationInfo (ILocationInfo locationInfo)
		{
			const string sqlStatement =
				"Insert into LocationInfo (Latitude, Longitude, CountryCode, CountryName, StateName, StateCode, Area, PostalCode) " +
				"Values (@Latitude, @Longitude, @CountryCode, @CountryName, @StateName, @StateCode, @Area, @PostalCode);";

			using (var conn = new SQLiteConnection(GetConnectionString(_GcTrackableRaceDbFileNameAndLocation)))
			{
				conn.Open();

				var cmd = new SQLiteCommand(sqlStatement, conn);

				cmd.Parameters.Add(new SQLiteParameter("@Latitude", locationInfo.Latitude));
				cmd.Parameters.Add(new SQLiteParameter("@Longitude", locationInfo.Longitude));
				cmd.Parameters.Add(new SQLiteParameter("@CountryCode", locationInfo.CountryCode));
				cmd.Parameters.Add(new SQLiteParameter("@CountryName", locationInfo.CountryName));
				cmd.Parameters.Add(new SQLiteParameter("@StateName", locationInfo.StateName));
				cmd.Parameters.Add(new SQLiteParameter("@StateCode", locationInfo.StateCode));
				cmd.Parameters.Add(new SQLiteParameter("@Area", locationInfo.Area));
				cmd.Parameters.Add(new SQLiteParameter("@PostalCode", locationInfo.PostalCode));
				
				cmd.ExecuteNonQuery();
			}
		}

		#region Private Methods

		private void CreateTables()
		{
			var tableCommands = new List<string>
				{
					"Create table LocationInfo (Latitude text, Longitude text, CountryCode text, CountryName text, StateName text, StateCode text, Area text, PostalCode text);",
					"Create unique index LocationInfoIndex ON LocationInfo (Latitude, Longitude);",
				};

			ExecuteSqlStatmentCommands(tableCommands);
		}

		private void ExecuteSqlStatmentCommands(IEnumerable<string> sqlCommands)
		{
			var dbConnection = new SQLiteConnection(GetConnectionString(_GcTrackableRaceDbFileNameAndLocation));

			dbConnection.Open();

			foreach (var sqlCommand in sqlCommands)
			{
				var command = new SQLiteCommand(sqlCommand, dbConnection);
				command.ExecuteNonQuery();
			}

			dbConnection.Close();
		}

		private void CreateDbFile()
		{
			if (!File.Exists(_GcTrackableRaceDbFileNameAndLocation))
			{
				SQLiteConnection.CreateFile(_GcTrackableRaceDbFileNameAndLocation);
			}
		}
		
		#endregion Private Methods
	}
}